package com.crm.common.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class POIUtilsLuoCheng {

    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";

    // 解析集合数据将其导出为表格
    public static void exportTable(List<List<String>> tableData, HttpServletResponse response, String tableName, List<String> sheetList) {


        // 创建表
        XSSFWorkbook workbook = new XSSFWorkbook();
        for (String sheet : sheetList) {
            XSSFSheet sheet1 = workbook.createSheet(sheet); // 创建分页
            int rowNum = 0; //行索引
            for (List<String> dataString : tableData) {
                XSSFRow row = sheet1.createRow(rowNum++); // 创建行
                int cellNum = 0; // 单元索引
                for (String cellDate : dataString) {
                    XSSFCell cell = row.createCell(cellNum++); // 创建单元
                    cell.setCellValue(cellDate); // 添加单元数据
                }
            }
        }

        // 输出表格 | 关闭资源
        try {
/*            // 暂时写死的本地测试路径
            FileOutputStream out = new FileOutputStream(new File("D:\\" + tableName + ".xlsx"));
            //通过输出流将内存中的Excel文件写入到磁盘上
            workbook.write(out);

            //关闭资源
            out.flush();
            out.close();
            workbook.close();*/

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename=" + tableName + ".xlsx");

            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();
            workbook.close();
        } catch (IOException io) {
            io.printStackTrace();
        }
    }

    // 解析表格的方法
    public static List<List<String>> parseExcel(MultipartFile file) throws IOException {

        // 检查数据
        checkFile(file);
        // 获取
        Workbook workBook = getWorkBook(file);
        // 获取每行的数据容器
        List<List<String>> data = new ArrayList<>();
        if (workBook != null) {
            for (int sheetNum = 0; sheetNum < workBook.getNumberOfSheets(); sheetNum++) {
                // 获取当前 sheet 工作表
                Sheet sheetAt = workBook.getSheetAt(sheetNum);
                if (sheetAt == null) {
                    continue; // 结束循环
                }
                Iterator<Row> rowIterator = sheetAt.iterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    List<String> rowDate = new ArrayList<>();

                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String cellValueAsString = getCellValueAsString(cell);
                        rowDate.add(cellValueAsString);
                    }
                    data.add(rowDate);
                }
            }
        }
        return data;
    }

    // 转换为字符串的方法
    private static String getCellValueAsString(Cell cell) {
        String cellValue = "";
        DecimalFormat decimalFormat = new DecimalFormat("#.######"); // 根据需要设置合适的格式
        if (cell.getCellTypeEnum() == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                cellValue = cell.getDateCellValue().toString(); // 处理日期类型的单元格
            } else {
                double numericValue = cell.getNumericCellValue();
                cellValue = decimalFormat.format(numericValue);
            }
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        return cellValue;
    }

    // 效验文件是否合法
    public static void checkFile(MultipartFile file) throws IOException {
        //判断文件是否存在
        if (null == file) {
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith(xls) && !fileName.endsWith(xlsx)) {
            throw new IOException(fileName + "不是excel文件");
        }
    }

    // 获取 Workbook 方法
    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith(xls)) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith(xlsx)) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
}
